/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DAO.InformationFilter;

import DAO.DAOAccess;
import Info.PageInfo;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author tran tuan tai
 */
public class PageDAO
{
    public Integer GetCategory(int pageID)
    {
        String sql = "SELECT CATEGORY FROM PAGE WHERE PAGEID = " + pageID;
        Connection conn = DAOAccess.GetConnection();
        try
        {
            PreparedStatement stat = conn.prepareStatement(sql);

            stat.executeQuery();
            ResultSet rs = stat.getResultSet();
            rs.beforeFirst();

            while (rs.next())
            {
                return rs.getInt(1);
            }

        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }

        return null;
    }

    public boolean IsExist(int pageID)
    {
        String query = "SELECT * FROM PAGE WHERE pageID = " + pageID;
        try
        {
            ResultSet rs =  DAOAccess.ExecuteQuery(query);
            
            //rs.beforeFirst();

            while(rs.next())
            {
                rs.close();
               return true;
            }
            rs.close();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }

        return false;
    }

    public List<PageInfo> GetAll()
    {
        String sql = "SELECT * FROM PAGE";
        return ExcuteQuery(sql);
    }


    public void DoEmpty()
    {
         try
        {
            java.sql.Connection conn = DAOAccess.GetConnection();
            String sql = "DELETE FROM PAGE";
            PreparedStatement stat = conn.prepareCall(sql);
            stat.execute();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
    }

    public List<PageInfo> GetByDate(Timestamp begin, Timestamp end)
    {
        String query = "SELECT  * " +
                " FROM PAGE WHERE TIME >= '" + begin +
                "' AND TIME < '" + end + "'";
        return ExcuteQuery(query);
    }

    public List<PageInfo> GetPages_BOWNotExist(int begin, int end)
    {
        String sql = "SELECT * FROM PAGE WHERE BOW='' OR BOW IS NULL LIMIT " + begin + "," + end;
        return ExcuteQuery(sql);
    }

    public PageInfo GetByID(int id)
    {
        String sql = "SELECT * FROM PAGE WHERE PAGEID = " + id;
        List<PageInfo> result = ExcuteQuery(sql);
        if (result.isEmpty())
        {
            return null;
        }
        else
        {
            return result.get(0);
        }
    }

    public List<PageInfo> ExcuteQuery(String query)
    {
        List<PageInfo> result = new ArrayList<PageInfo>();

        try
        {
            ResultSet rs =  DAOAccess.ExecuteQuery(query);

            int idxID = rs.findColumn("id");
            int idxPageID = rs.findColumn("pageid");
            int idxMyLink = rs.findColumn("mylink");
            int idxContent = rs.findColumn("content");
            int idxBow = rs.findColumn("bow");
            int idxBoc = rs.findColumn("boc");
            int idxTime = rs.findColumn("time");
            int idxCategory = rs.findColumn("category");

            rs.beforeFirst();

            while(rs.next())
            {
                PageInfo pageInfo = new PageInfo();
                pageInfo.SetID(rs.getInt(idxID));
                pageInfo.SetMyLink(rs.getString(idxMyLink));
                pageInfo.SetContent(rs.getString(idxContent));
                pageInfo.SetMyPageID(rs.getInt(idxPageID));
                pageInfo.SetBOC(rs.getString(idxBoc));
                pageInfo.SetBOW(rs.getString(idxBow));
                pageInfo.SetCategory(rs.getInt(idxCategory));
                pageInfo.SetTime(rs.getTimestamp(idxTime));

                result.add(pageInfo);
            }
            rs.close();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }

        return result;
    }

    public int Insert(int pageID, String mylink, String content, Timestamp time)
    {
        int id = -1;
        try
        {
            java.sql.Connection conn = DAOAccess.GetConnection();
            String sql = "INSERT INTO PAGE(PAGEID, MYLINK, CONTENT, TIME) VALUES(?, ?, ?, ?)";

            PreparedStatement statement = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            statement.setInt(1, pageID);
            statement.setString(2, mylink);
            statement.setString(3, content);
            statement.setTimestamp(4, time);

            statement.executeUpdate();
            ResultSet result = statement.getGeneratedKeys();
            result.first();
            id = result.getInt(1);
            statement.close();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }

        return id;
    }

    public void UpdateBOW(int pageID, String BOW)
    {
        try
        {
            java.sql.Connection conn = DAOAccess.GetConnection();
            String sql = "UPDATE PAGE SET BOW = ? WHERE PAGEID = ?";

            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, BOW);
            statement.setInt(2, pageID);

            statement.executeUpdate();
            statement.close();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
    }

    public void UpdateBOC(int pageID, String BOC)
    {
        try
        {
            java.sql.Connection conn = DAOAccess.GetConnection();
            String sql = "UPDATE PAGE SET BOC = ? WHERE PAGEID = ?";

            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, BOC);
            statement.setInt(2, pageID);

            statement.executeUpdate();          
            statement.close();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
    }
    public void UpdateCategory(String pageID, int categoryID)
    {
            try
        {
            java.sql.Connection conn = DAOAccess.GetConnection();
            String sql = "UPDATE PAGE SET CATEGORY = ? WHERE PAGEID = ?";

            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setInt(1, categoryID);
            statement.setString(2, pageID);

            statement.executeUpdate();           
            statement.close();
        }
        catch(Exception ex)
        {
            ex.printStackTrace();
        }
    }
}
